package information;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import admin.TestPro;



public class InformationJdbc {
	public void InsertPswQue(String question, String answer,String id) throws Exception {
		String sql="insert into information(question,answer) value(?,?) where id=?";
		Class.forName(TestPro.readProper("name"));
		String url=TestPro.readProper("url");
		java.sql.Connection con = DriverManager.getConnection(url, TestPro.readProper("username"),TestPro.readProper("psw"));
		PreparedStatement pre=con.prepareStatement(sql);
		pre.setString(1, question);
		pre.setString(2, answer);
		pre.execute();
		con.close();
	}
	public void InsertBirthday(String id,String name,String year, String month, String day,String sex,int age,String password) throws Exception {
		String sql="insert into information(id,name,year,month,birthday,sex,age,password) value(?,?,?,?,?,?,?,?)";
		Class.forName(TestPro.readProper("name"));
		String url=TestPro.readProper("url");
		java.sql.Connection con = DriverManager.getConnection(url, TestPro.readProper("username"),TestPro.readProper("psw"));
		PreparedStatement pre=con.prepareStatement(sql);
		pre.setString(1, id);
		pre.setString(2, name);
		pre.setString(3, year);
		pre.setString(4, month);
		pre.setString(5, day);
		pre.setString(6, sex);
		pre.setInt(7, age);
		pre.setString(8, password);
		pre.execute();
		con.close();
	}

	public List<Information> queryInfoById(String id) throws Exception {
		String sql="select * from information where id=?";
		Class.forName(TestPro.readProper("name"));
		String url=TestPro.readProper("url");
		java.sql.Connection con = DriverManager.getConnection(url, TestPro.readProper("username"),TestPro.readProper("psw"));
		PreparedStatement pre=con.prepareStatement(sql);
		pre.setString(1, id);
		ResultSet res=pre.executeQuery();
		List<Information> list=new ArrayList<Information>();
		while(res.next()){
			String name=res.getString(2);
			String psw=res.getString(3);
			String email=res.getString(4);
			String sex=res.getString(5);
			String year=res.getString(6);
			String month=res.getString(7);
			String birthday=res.getString(8);
			int age=res.getInt(9);
			String question=res.getString(10);
			String answer=res.getString(11);
			Information information=new Information(id,name,email,sex,year,month,birthday,age,question,answer,psw);
			list.add(information);
		}
		return list;
	}
	public void UpdateInfo(String id, String name, String email,String year, String month, String day, String sex, int age,String question,String answer) throws Exception {
		String sql="update information set name=?,email=?,year=?,month=?,birthday=?,sex=?,age=?,question=?,answer=? where id=?";
		Class.forName(TestPro.readProper("name"));
		String url=TestPro.readProper("url");
		java.sql.Connection con = DriverManager.getConnection(url, TestPro.readProper("username"),TestPro.readProper("psw"));
		PreparedStatement pre=con.prepareStatement(sql);
		pre.setString(1, name);
		pre.setString(2, email);
		pre.setString(3, year);
		pre.setString(4, month);
		pre.setString(5, day);
		pre.setString(6, sex);
		pre.setInt(7, age);
		pre.setString(8, question);
		pre.setString(9, answer);
		pre.setString(10, id);
		pre.execute();
		con.close();
		
	}
	public static Connection getCon() throws Exception{
		InformationJdbc qj=new InformationJdbc();
		String driver=qj.readProper("jdbc.properties","driver");
		String url=qj.readProper("jdbc.properties","url");
		String root=qj.readProper("jdbc.properties","username");
		String password=qj.readProper("jdbc.properties","password");
		Class.forName(driver);
		Connection con= DriverManager.getConnection(url,root,password);
		return con;
	}

	public String readProper(String file,String key) throws Exception{
		Properties pro=new Properties();
		ClassLoader cl=InformationJdbc.class.getClassLoader();
		InputStream is=cl.getResourceAsStream(file);
		pro.load(is);
		String val=pro.getProperty(key);
		return val;
	}
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}
	public void UpdatePswById(String id,String password) throws Exception {
		// TODO Auto-generated method stub
		String sql="update information set password=? where id=?";
		String sql1="update user set password=? where userid=?";
		Class.forName(TestPro.readProper("name"));
		String url=TestPro.readProper("url");
		java.sql.Connection con = DriverManager.getConnection(url, TestPro.readProper("username"),TestPro.readProper("psw"));
		PreparedStatement pre=con.prepareStatement(sql);
		PreparedStatement pre1=con.prepareStatement(sql1);
		pre.setString(1, password);
		pre.setString(2, id);
		pre1.setString(1, password);
		pre1.setString(2, id);
		pre.execute();
		pre1.execute();
		con.close();
		
	}

}
